Step 5: SQL Queries & Use Cases — Amazon (Shopping)

These SQL queries represent real e-commerce transactional database design patterns at FAANG scale. They cover catalog filtering, cart coupon application, stock reservations, order timelines, shipment tracking, analytics leaderboards, and collaborative recommendation feeds.

1. Product Search with Hierarchy Filtering & Reviews

Retrieves product variants matching a search query, mapping parent/child categories, and returning average customer star ratings.

WITH RECURSIVE category_tree AS (
    -- Anchor member: Select specified root category
    SELECT category_id, parent_category_id, name
    FROM categories
    WHERE category_id = :filter_category_id

    UNION ALL

    -- Recursive member: Add sub-categories
    SELECT c.category_id, c.parent_category_id, c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_category_id = ct.category_id
),
product_ratings AS (
    -- Pre-calculate average rating per product
    SELECT product_id, ROUND(AVG(rating), 2) AS avg_rating, COUNT(*) AS total_reviews
    FROM reviews
    GROUP BY product_id
)
SELECT 
    pv.variant_id,
    p.title AS product_name,
    p.brand,
    c.name AS category_name,
    pv.sku,
    pv.price,
    pv.attributes,
    COALESCE(pr.avg_rating, 0.00) AS rating,
    COALESCE(pr.total_reviews, 0) AS review_count,
    -- Inventory indicator: sum stock across all warehouses
    (SELECT SUM(quantity_available) FROM inventories WHERE variant_id = pv.variant_id) AS stock_available
FROM product_variants pv
JOIN products p ON pv.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
LEFT JOIN product_ratings pr ON p.product_id = pr.product_id
WHERE (p.title ILIKE '%' || :search_term || '%' OR p.description ILIKE '%' || :search_term || '%')
  AND p.category_id IN (SELECT category_id FROM category_tree)
ORDER BY pv.price ASC
LIMIT :limit OFFSET :offset;

2. Shopping Cart Price Calculator with Coupons & Prime Delivery Rules

Retrieves cart items, calculates item totals, checks discount values, and enforces shipping rules (expedited free shipping for Amazon Prime members or orders over $35).

WITH cart_details AS (
    -- Calculate items weight and total cost in cart
    SELECT 
        ci.cart_id,
        SUM(pv.price * ci.quantity) AS subtotal,
        SUM(pv.weight_kg * ci.quantity) AS total_weight_kg
    FROM cart_items ci
    JOIN product_variants pv ON ci.variant_id = pv.variant_id
    WHERE ci.cart_id = :cart_id
    GROUP BY ci.cart_id
),
applied_coupon AS (
    -- Check validity of Coupon Code entered
    SELECT coupon_id, discount_type, value, max_discount
    FROM coupons
    WHERE code = :coupon_code
      AND start_date <= NOW() AND end_date >= NOW()
),
discount_calc AS (
    -- Calculate discount deductables
    SELECT 
        cd.cart_id,
        cd.subtotal,
        cd.total_weight_kg,
        CASE 
            WHEN ac.discount_type = 'PERCENTAGE' THEN 
                LEAST((cd.subtotal * ac.value / 100.0), COALESCE(ac.max_discount, 99999.99))
            WHEN ac.discount_type = 'FLAT' THEN ac.value
            ELSE 0.00
        END AS discount_deductible
    FROM cart_details cd
    LEFT JOIN applied_coupon ac ON TRUE
)
SELECT 
    dc.subtotal,
    dc.discount_deductible AS coupon_discount,
    -- Post-coupon total
    (dc.subtotal - dc.discount_deductible) AS net_subtotal,
    -- Tax calculation (standard 8% sales tax)
    ROUND((dc.subtotal - dc.discount_deductible) * 0.08, 2) AS estimated_tax,
    -- Shipping logic: $0 for Prime or net_subtotal >= $35, otherwise $4.99 flat
    CASE 
        WHEN u.is_prime = TRUE OR (dc.subtotal - dc.discount_deductible) >= 35.00 THEN 0.00
        ELSE 4.99
    END AS shipping_cost,
    -- Final checkout amount
    ( (dc.subtotal - dc.discount_deductible) + 
      ROUND((dc.subtotal - dc.discount_deductible) * 0.08, 2) + 
      (CASE WHEN u.is_prime = TRUE OR (dc.subtotal - dc.discount_deductible) >= 35.00 THEN 0.00 ELSE 4.99 END)
    ) AS grand_total
FROM discount_calc dc
JOIN carts c ON dc.cart_id = c.cart_id
JOIN users u ON c.user_id = u.user_id;

3. Concurrency-Safe Stock Reservation (Pre-Checkout Lock)

Attempts to reserve stock for checkout. Employs optimistic/concurrency checks to prevent race conditions during flash sale events.

UPDATE inventories
SET 
    quantity_available = quantity_available - :checkout_quantity,
    quantity_reserved = quantity_reserved + :checkout_quantity
WHERE variant_id = :variant_id
  AND center_id = (
      -- Choose warehouse closest to delivery or one holding sufficient stock
      SELECT center_id 
      FROM inventories 
      WHERE variant_id = :variant_id AND quantity_available >= :checkout_quantity
      LIMIT 1
  )
  -- Crucial race-condition guard: Ensures stock is not depleted before execution completes
  AND quantity_available >= :checkout_quantity;

4. Customer Order History Dashboard

Displays a user's past orders with order items, pricing snapshots, shipments, and tracking statuses.

SELECT 
    o.order_id,
    o.total_amount,
    o.tax_amount,
    o.shipping_cost,
    o.status AS order_status,
    o.created_at AS order_date,
    -- Sub-query aggregating order items
    (SELECT json_agg(json_build_object(
        'product_name', p.title,
        'sku', pv.sku,
        'quantity', oi.quantity,
        'price_paid', oi.price_per_unit,
        'attributes', pv.attributes
     ))
     FROM order_items oi
     JOIN product_variants pv ON oi.variant_id = pv.variant_id
     JOIN products p ON pv.product_id = p.product_id
     WHERE oi.order_id = o.order_id) AS items,
    -- Shipment details
    os.carrier,
    os.tracking_number,
    os.status AS shipment_status,
    os.estimated_delivery
FROM orders o
LEFT JOIN order_shipments os ON o.order_id = os.order_id
WHERE o.user_id = :current_user_id
ORDER BY o.created_at DESC;

5. Logistics Package Transit Logs Tracking

Retrieves shipment movements for tracking notifications.

SELECT 
    os.tracking_number,
    os.carrier,
    os.status AS current_status,
    os.estimated_delivery,
    -- Chronological movement details
    (SELECT json_agg(json_build_object(
        'location', stl.location,
        'activity', stl.activity_description,
        'timestamp', stl.logged_at
     ) ORDER BY stl.logged_at DESC)
     FROM shipment_tracking_logs stl
     WHERE stl.shipment_id = os.shipment_id) AS tracking_history
FROM order_shipments os
WHERE os.tracking_number = :tracking_number;

6. Category Sales Leaderboard (Weekly Analytics)

Computes the highest-selling products in each top-level category within the last 7 days using SQL window functions.

WITH category_sales AS (
    SELECT 
        c.category_id,
        c.name AS category_name,
        p.product_id,
        p.title AS product_name,
        SUM(oi.quantity) AS total_units_sold,
        SUM(oi.quantity * oi.price_per_unit) AS total_revenue,
        -- Determine category rank
        DENSE_RANK() OVER (
            PARTITION BY c.category_id 
            ORDER BY SUM(oi.quantity * oi.price_per_unit) DESC
        ) AS sales_rank
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    JOIN product_variants pv ON oi.variant_id = pv.variant_id
    JOIN products p ON pv.product_id = p.product_id
    JOIN categories c ON p.category_id = c.category_id
    WHERE o.status = 'PAID' AND o.created_at >= NOW() - INTERVAL '7 days'
    GROUP BY c.category_id, c.name, p.product_id, p.title
)
SELECT 
    category_id,
    category_name,
    product_id,
    product_name,
    total_units_sold,
    total_revenue
FROM category_sales
WHERE sales_rank <= 5 -- Top 5 per category
ORDER BY category_name ASC, sales_rank ASC;

7. Product Review Helpmate Ranking Algorithm

Retrieves reviews for a product, sorting reviews dynamically based on helpfulness votes minus unhelpful votes, prioritizing verified purchases.

SELECT 
    r.review_id,
    r.rating,
    r.headline,
    r.comment,
    r.verified_purchase,
    r.created_at,
    u.full_name AS reviewer_name,
    -- Helpful count
    (SELECT COUNT(*) FROM review_helpful_votes h WHERE h.review_id = r.review_id) AS helpful_votes,
    -- Review Score Rank (verified purchases rank higher; older reviews decay slightly)
    ROUND(
        ( (SELECT COUNT(*) FROM review_helpful_votes h WHERE h.review_id = r.review_id) + 
          (CASE WHEN r.verified_purchase = TRUE THEN 5 ELSE 0 END) )
        / POWER(EXTRACT(EPOCH FROM (NOW() - r.created_at))/86400 + 2, 1.2)::numeric, 4
    ) AS review_score
FROM reviews r
JOIN users u ON r.user_id = u.user_id
WHERE r.product_id = :product_id
ORDER BY review_score DESC, r.created_at DESC;

8. Coupon Validator

Checks discount code eligibility prior to application.

SELECT 
    coupon_id,
    code,
    discount_type,
    value,
    max_discount,
    CASE 
        WHEN end_date < NOW() THEN 'EXPIRED'
        WHEN usage_limit IS NOT NULL AND 
             (SELECT COUNT(*) FROM user_coupons WHERE coupon_id = coupons.coupon_id) >= usage_limit THEN 'LIMIT_REACHED'
        WHEN EXISTS (
             SELECT 1 FROM user_coupons 
             WHERE coupon_id = coupons.coupon_id AND user_id = :current_user_id
        ) THEN 'ALREADY_USED'
        ELSE 'VALID'
    END AS eligibility_status
FROM coupons
WHERE code = :coupon_code;

9. Customer Support Ticket Metrics

Helper query for admin dashboard reporting.

SELECT 
    status,
    COUNT(*) AS ticket_count,
    -- Average resolution time (days)
    AVG(EXTRACT(DAY FROM (NOW() - created_at))) AS avg_days_open
FROM customer_support_tickets
GROUP BY status;

10. Amazon Collaborative Filtering Engine (Customers Who Bought This Also Bought)

Identifies products frequently purchased in tandem with a specific product.

WITH co_purchased_variants AS (
    -- Find variants bought in the same orders as target product variants
    SELECT oi_other.variant_id, COUNT(*) AS purchase_frequency
    FROM order_items oi_target
    JOIN order_items oi_other ON oi_target.order_id = oi_other.order_id
    WHERE oi_target.variant_id IN (
        SELECT variant_id FROM product_variants WHERE product_id = :target_product_id
    )
    AND oi_other.variant_id NOT IN (
        SELECT variant_id FROM product_variants WHERE product_id = :target_product_id
    )
    GROUP BY oi_other.variant_id
)
SELECT 
    p.product_id,
    p.title AS recommended_product_name,
    p.brand,
    pv.price,
    cp.purchase_frequency
FROM co_purchased_variants cp
JOIN product_variants pv ON cp.variant_id = pv.variant_id
JOIN products p ON pv.product_id = p.product_id
ORDER BY cp.purchase_frequency DESC
LIMIT 5;

This content is reserved for Premium Members.